﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.SqlClient;

namespace TB.DALSqlProvider
{
    /// <summary>
    /// 对*VisitLog*操作数据库(针对SQL Server)的实现
    /// </summary>
    public class VisitLogSqlDALProvider : IDAL.IDALVisitLog
    {
        public int Insert(TB.Entity.VisitLog obj)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_VisitLog_insert") as SqlCommand;
            //3.cmd参数赋值
            //cmd.Parameters.AddWithValue("@id_auto", obj.id_auto == 0 ? DBNull.Value : (object)obj.id_auto);
            cmd.Parameters.AddWithValue("@username", obj.username == String.Empty ? DBNull.Value : (object)obj.username);
            cmd.Parameters.AddWithValue("@nickname", obj.nickname == String.Empty ? DBNull.Value : (object)obj.nickname);
            cmd.Parameters.AddWithValue("@visitDate", obj.visitDate);
            cmd.Parameters.AddWithValue("@message", obj.message == String.Empty ? DBNull.Value : (object)obj.message);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }

        public int Update(TB.Entity.VisitLog obj)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_VisitLog_update") as SqlCommand;
            //3.cmd参数赋值
            cmd.Parameters.AddWithValue("@id_auto", obj.id_auto == 0 ? DBNull.Value : (object)obj.id_auto);
            cmd.Parameters.AddWithValue("@username", obj.username == String.Empty ? DBNull.Value : (object)obj.username);
            cmd.Parameters.AddWithValue("@nickname", obj.nickname == String.Empty ? DBNull.Value : (object)obj.nickname);
            cmd.Parameters.AddWithValue("@visitDate", obj.visitDate == DateTime.Now ? DBNull.Value : (object)obj.visitDate);
            cmd.Parameters.AddWithValue("@message", obj.message == String.Empty ? DBNull.Value : (object)obj.message);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }

        public int Delete(TB.Entity.VisitLog obj)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_VisitLog_delete") as SqlCommand;
            //3.cmd参数赋值
            cmd.Parameters.AddWithValue("@id_auto", obj.id_auto);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }

        public List<TB.Entity.VisitLog> LoadAll()
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_VisitLog_loadAll") as SqlCommand;
            //3.通过db执行一个DBReader
            var reader = db.ExecuteReader(cmd);
            //4.遍历DBReader,生成新的Entity对象,然后添加到List
            List<TB.Entity.VisitLog> list = new List<TB.Entity.VisitLog>();
            while (reader.Read())
            {
                list.Add(new TB.Entity.VisitLog()
                {
                    id_auto = reader["id_auto"] == DBNull.Value ? 0 : (int)reader["id_auto"],
                    username = reader["username"] == DBNull.Value ? String.Empty : (string)reader["username"],
                    nickname = reader["nickname"] == DBNull.Value ? String.Empty : (string)reader["nickname"],
                    visitDate = reader["visitDate"] == DBNull.Value ? DateTime.Now : (DateTime)reader["visitDate"],
                    message = reader["message"] == DBNull.Value ? String.Empty : (string)reader["message"],
                });
            }
            return list;
        }

        public TB.Entity.VisitLog LoadById(int id)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_VisitLog_loadById") as SqlCommand;
            //3.通过db执行一个DBReader
            cmd.Parameters.AddWithValue("@id", id);
            var reader = db.ExecuteReader(cmd);
            //4.遍历DBReader,生成一个新的Entity对象 
            TB.Entity.VisitLog entity = null;
            while (reader.Read())
            {
                entity = new TB.Entity.VisitLog()
                {
                    id_auto = reader["id_auto"] == DBNull.Value ? 0 : (int)reader["id_auto"],
                    username = reader["username"] == DBNull.Value ? String.Empty : (string)reader["username"],
                    nickname = reader["nickname"] == DBNull.Value ? String.Empty : (string)reader["nickname"],
                    visitDate = reader["visitDate"] == DBNull.Value ? DateTime.Now : (DateTime)reader["visitDate"],
                    message = reader["message"] == DBNull.Value ? String.Empty : (string)reader["message"],
                };
            }
            return entity;
        }

        public System.Data.DataSet GetDataSet(string strSql)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var cmd = db.GetSqlStringCommand(strSql) as SqlCommand;
            System.Data.DataSet ds = db.ExecuteDataSet(cmd);
            return ds;
        }
    }
}